﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Configuration;
using System.Windows.Forms;
using System.IO;
using AG.Utilities;
using DBUtil;


namespace SQLServerHealthCheck.SQLServer
{
    public static class DatabaseInstaller
    {
        public static void Install(String instanceName)
        {
            String connectionString = string.Format("Server={0};Database={1};Integrated Security=True;", instanceName, "SQLServerOptimizer");
            String connectionStringMaster = string.Format("Server={0};Database={1};Integrated Security=True;", instanceName, "Master");

            //Check app.config
            if (CollectServerAndServiceData.IsSQLServerAccessible(connectionStringMaster))
            {
                try
                {

                    //Load SQL script from SQLServerHealthCheck.Database.sql

                    string FileName = Application.StartupPath + @"\Database\SQLServerHealthCheck.Database.sql";

                    FileInfo fileInfo = new FileInfo(FileName);
                    String sqlDatabaseScript = fileInfo.OpenText().ReadToEnd();

                    //Replace SQL Variables:
                    sqlDatabaseScript = sqlDatabaseScript.Replace(@":setvar DatabaseName ""SQLServerOptimizer""", "");
                    sqlDatabaseScript = sqlDatabaseScript.Replace(@":setvar DefaultFilePrefix ""SQLServerOptimizer""", "");
                    sqlDatabaseScript = sqlDatabaseScript.Replace(@":setvar DefaultDataPath ""C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\""", "");
                    sqlDatabaseScript = sqlDatabaseScript.Replace(@":setvar DefaultLogPath ""C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\""", "");
                    sqlDatabaseScript = sqlDatabaseScript.Replace(@":on error exit", "");
                    sqlDatabaseScript = sqlDatabaseScript.Replace(@":setvar __IsSqlCmdEnabled ""True""", "");

                    sqlDatabaseScript = sqlDatabaseScript.Replace(@"$(__IsSqlCmdEnabled)", "True");
                    sqlDatabaseScript = sqlDatabaseScript.Replace(@"$(DatabaseName)", "SQLServerOptimizer");
                    sqlDatabaseScript = sqlDatabaseScript.Replace(@"$(DefaultDataPath)", GetDataFilePath(instanceName));
                    sqlDatabaseScript = sqlDatabaseScript.Replace(@"$(DefaultLogPath)", GetLogFilePath(instanceName));
                    sqlDatabaseScript = sqlDatabaseScript.Replace(@"$(DefaultFilePrefix)", "SQLServerOptimizer");
                    

                    StringReader reader = new StringReader(sqlDatabaseScript);

                    //Excecute script using special object SqlCmd that simulates the SQLCMD mode of SQL Server (SQLCommand cannot handle GO)
                    using (SqlConnection connection = new SqlConnection(connectionStringMaster))
                    {
                        connection.Open();

                        SqlCmd cmd = new SqlCmd(connection);

                        cmd.ExecuteFile(reader);

                        //Server server = new Server(new ServerConnection(connection));
                        //server.ConnectionContext.ExecuteNonQuery(sqlDatabaseScript);

                    }
                    //Change Connectionstring ConnectionStringSSHCDatabase in appsettings
                    System.Configuration.Configuration oConfig = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
                    oConfig.AppSettings.Settings["ConnectionStringSSHCDatabase"].Value = connectionString;
                    oConfig.AppSettings.Settings["DatabaseCreated"].Value = "true";
                    oConfig.Save(ConfigurationSaveMode.Modified);
                    ConfigurationManager.RefreshSection("appSettings");
                }
                catch (Exception e)
                {
                    Logger.Error(e);
                }

            }
        }


        private static string GetDataFilePath(string databaseserver)
        {
            string strDataFilepath = "";
            try
            {

                SqlConnection conn = new SqlConnection(@"Server=" + databaseserver + ";Database=master;Integrated Security=True");
                conn.Open();
                SqlCommand ocmd = new SqlCommand("select SUBSTRING([filename],0, CHARINDEX([name],[filename],0)) as DataFilePath from sys.sysaltfiles where dbid=1 and fileid=1", conn);
                SqlDataReader rd = ocmd.ExecuteReader();

                if (rd.Read())
                {

                    strDataFilepath = rd["DataFilePath"].ToString();
                }

                rd.Close();

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            


            return strDataFilepath;

        }

        private static string GetLogFilePath(string databaseserver)
        {
            string strLogFilepath = "";
            try
            {
                SqlConnection conn = new SqlConnection(@"Server=" + databaseserver + ";Database=master;Integrated Security=true");
                conn.Open();
                SqlCommand ocmd = new SqlCommand("select SUBSTRING([filename],0, CHARINDEX([name],[filename],0))  as LogFilePath from sys.sysaltfiles where dbid=1 and fileid=2", conn);
                SqlDataReader rd = ocmd.ExecuteReader();

                if (rd.Read())
                {

                    strLogFilepath = rd["LogFilePath"].ToString();
                }

                rd.Close();
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }


            return strLogFilepath;

        }


    }
}
